<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
    class Messages_m extends CI_Model {
        
        function get_inbox($id) {
            $sql = "SELECT *, CONCAT(coord_lname,', ',coord_fname) AS name FROM messages JOIN user_messages ON messages.msg_id = user_messages.msg_id
                JOIN coordinator ON user_messages.sender_id = coordinator.coordinator_id WHERE user_messages.msg_type = 0 AND 
                user_messages.recipient_id = ? ORDER BY messages.msg_id DESC";
            $q = $this->db->query($sql, $id);
            if ($q->num_rows() > 0) {
                return $q->result_array();
            } else {
                return FALSE;
            }
        }
        
        function get_sentbox($id) {
            $sql = "SELECT *, CONCAT(coord_lname,', ',coord_fname) AS name FROM messages JOIN user_messages ON messages.msg_id = user_messages.msg_id
                JOIN coordinator ON user_messages.recipient_id = coordinator.coordinator_id WHERE user_messages.msg_type = 1 AND 
                user_messages.sender_id = ? ORDER BY messages.msg_id DESC";
            $q = $this->db->query($sql, $id);
            if ($q->num_rows() > 0) {
                return $q->result_array();
            } else {
                return FALSE;
            }
        }
//        function get_draft($id) {
//            $sql = "SELECT *, CONCAT(coord_lname,', ',coord_fname) AS name FROM messages JOIN user_messages ON messages.msg_id = user_messages.msg_id
//                JOIN coordinator ON user_messages.recipient = coordinator.coordinator_id WHERE user_messages.msg_type = 2 AND 
//                user_messages.sender = ? ORDER BY messages.msg_id DESC";
//            $q = $this->db->query($sql, $id);
//            if ($q->num_rows() > 0) {
//                return $q->result_array();
//            } else {
//                return FALSE;
//            }
//        }
        function inbox_msg($id) {
            $q = $this->db->select('coordinator_id,coord_lname,coord_fname,user_messages.msg_id, messages.subject, messages.msg_body, messages.date, messages.attach_name, messages.attach_path')
                    ->from('coordinator')->join('user_messages', 'coordinator.coordinator_id = user_messages.sender_id')
                    ->join('messages', 'user_messages.msg_id = messages.msg_id')->where('messages.msg_id', $id)
                    ->where('msg_type', '0')->get();
            if ($q->num_rows() > 0) {
                return $q->row();
            } else {
                return FALSE;
            }
        }
        function sentbox_msg($id) {
            $q = $this->db->select('coordinator_id,coord_lname,coord_fname,user_messages.msg_id, messages.subject, messages.msg_body, messages.date, messages.attach_name, messages.attach_path')
                    ->from('coordinator')->join('user_messages', 'coordinator.coordinator_id = user_messages.recipient_id')
                    ->join('messages', 'user_messages.msg_id = messages.msg_id')->where('messages.msg_id', $id)
                    ->where('msg_type', '1')->get();
            if ($q->num_rows() > 0) {
                return $q->row();
            } else {
                return FALSE;
            }
        }
//        function draft_msg($id) {
//            $q = $this->db->select('coordinator_id,coord_lname,coord_fname,user_messages.msg_id, messages.subject, messages.msg_body, messages.date,messages.attach_name,messages.attach_path')
//                    ->from('coordinator')->join('user_messages', 'coordinator.coordinator_id = user_messages.sender')
//                    ->join('messages', 'user_messages.msg_id = messages.msg_id')->where('messages.msg_id', $id)
//                    ->where('msg_type', '2')->get();
//            if ($q->num_rows() > 0) {
//                return $q->row();
//            } else {
//                return FALSE;
//            }
//        }
        function get_max_msg_id(){
            $q = $this->db->select('msg_id')->from('messages')->order_by('msg_id', 'DESC')->limit(1)->get();
            if ($q->num_rows() > 0) {
                return $q->row();
            } else {
                return FALSE;
            }
        }
        
        function get_unread_msg_num($id) {
            $q = $this->db->select('*')->from('coordinator')->join('user_messages', 'coordinator.coordinator_id = user_messages.recipient_id')
                    ->join('messages', 'user_messages.msg_id = messages.msg_id')->where('coordinator.coordinator_id', $id)
                    ->where('user_messages.read', 1)->where('user_messages.recipient_id', $id)->get();
            return $q->num_rows();
        }
        
        function insert_user_messages($msg_id, $data) {
            if($data['action']==1){
                $user_msg_inbox = array(
                    'msg_id' => $msg_id,
                    'sender_id' => $data['sender_id'],
                    'recipient_id' => $data['recipient_id'],
                    'msg_type' => 2,
                    'read' => $data['read'],
                );
                $this->db->insert('user_messages', $user_msg_inbox);
            }else{
                $user_msg_inbox = array(
                    'msg_id' => $msg_id,
                    'sender_id' => $data['sender_id'],
                    'recipient_id' => $data['recipient_id'],
                    'msg_type' => 0,
                    'read' => $data['read'],
                );
                $this->db->insert('user_messages', $user_msg_inbox);
                $user_msg_sentnbox = array(
                    'msg_id' => $msg_id,
                    'sender_id' => $data['sender_id'],
                    'recipient_id' => $data['recipient_id'],
                    'msg_type' => 1,
                    'read' => $data['read'],
                );
                $this->db->insert('user_messages', $user_msg_sentnbox);
            }
            return $this->db->affected_rows();
        }
        
        function insert_msg($data){
            $msg = array(
                'subject' => $data['subject'],
                'msg_body' => $data['msg_body'],
                'date' => $data['date'],
                'attach_name' => $data['attach_name'],
                'attach_path' => $data['attach_path'],
            );
            $this->db->insert('messages', $msg);
            $msg_id = $this->db->insert_id();
            return $this->insert_user_messages($msg_id, $data);
        }
        
        function change_status($data){
            $this->db->where('recipient_id', $acc_id)->where('msg_id', $msg_id)->update('user_messages', $data);
        }
        
        function delete_msg_inbox($acc_id, $msg_id) {
            for($i=0;$i<sizeof($msg_id);$i++){
                $q = $this->db->select('attach_path, attach_name')->from('messages')->where('messages.msg_id', $msg_id[$i])->get()->row();
                $this->db->where('recipient_id', $acc_id)->where('msg_id', $msg_id[$i])
                        ->where('msg_type', 0)->delete('user_messages');
                $this->db->where('msg_id', $msg_id[$i])->delete('messages');
                $msg_deleted = $this->db->affected_rows();
                if($msg_deleted>0){
                    $msg_attach_path = $q;
                    if($msg_attach_path->attach_path != NULL){
                        unlink($msg_attach_path->attach_path);
                    }
                }
            }
        }
        
        function delete_msg_sentbox($acc_id, $msg_id) {
            for($i=0;$i<sizeof($msg_id);$i++){
                $q = $this->db->select('attach_path, attach_name')->from('messages')->where('messages.msg_id', $msg_id[$i])->get()->row();
                $this->db->where('sender_id', $acc_id)->where('msg_id', $msg_id[$i])
                        ->where('msg_type', 1)->delete('user_messages');
                $this->db->where('msg_id', $msg_id[$i])->delete('messages');
                $msg_deleted = $this->db->affected_rows();
                if($msg_deleted>0){
                    $msg_attach_path = $q;
                    if($msg_attach_path->attach_path != NULL){
                        unlink($msg_attach_path->attach_path);
                    }
                }
            }
            
        }
    }
